package com.scalar.chiptrack.jasper.admin.actions;

import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.jasper.base.AbstractJasperReportsAction;
import com.scalar.chiptrack.jasper.base.datasource.ChiptrackJasperDataSource;
import com.scalar.chiptrack.jasper.base.datasource.JasperDataSourceFactory;
import com.scalar.chiptrack.utils.StringUtils;

import javax.servlet.http.HttpServletRequest;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Map;

/**
 *
 */
public class UserReportAction extends AbstractJasperReportsAction {

	@Override
	public ChiptrackJasperDataSource getJasperDataSource(Map paramMap, HttpServletRequest request) throws Exception {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		try {
			boolean isWhereClauseAdded = false;
			String query = "SELECT User_Table.User_ID, User_Table.First_Name, User_Table.Last_Name, User_Table.Email, User_Table.Phone_Number, User_Table.Fax_Number, User_Table.Disabled, " +
					"User_Table.Expires_On, User_Table.Created_Date, User_Group_Table.Group_Name FROM\n" +
					"User_Table INNER JOIN User_Group_Table ON User_Table.Group_ID = User_Group_Table.Group_ID ";
			if (!StringUtils.isNullOrEmpty((String) paramMap.get("prompt0"))) {
				query += " where User_Table.First_Name LIKE '" + paramMap.get("prompt0") + "'";
				isWhereClauseAdded = true;
			}
			if (!StringUtils.isNullOrEmpty((String) paramMap.get("prompt1"))) {
				query += isWhereClauseAdded ? " and " : " where ";
				query += " User_Table.Last_Name LIKE '" + paramMap.get("prompt1") + "'";
				isWhereClauseAdded = true;
			}
			if (!StringUtils.isNullOrEmpty((String) paramMap.get("prompt2"))) {
				query += isWhereClauseAdded ? " and " : " where ";
				query += " User_Group_Table.Group_Name LIKE '" + paramMap.get("prompt2") + "'";
				isWhereClauseAdded = true;
			}
			conn = DataSource.getConnection();
			pstmt = conn.prepareStatement(query);
			rset = pstmt.executeQuery();
			ChiptrackJasperDataSource chiptrackJasperDataSource = JasperDataSourceFactory.getDataSource(rset);
			return chiptrackJasperDataSource;
		} finally {
			if (rset != null) {
				try {
					rset.close();
				} catch (SQLException ignore) {
				}
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException ignore) {
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException ignore) {
				}
			}
		}
	}
}
